package helloworld.dao.msaccess;

import helloworld.dao.NewsDao;

import helloworld.admin.bean.NewsAdminBean;
import helloworld.dao.DAOException;
import helloworld.exception.DuplicateNewsException;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.ArrayList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import helloworld.exception.ErrorLevel;

import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
import helloworld.common.DbSQLCommon;
import helloworld.common.CommonUtils;
import java.net.URLDecoder; 
/**
 * NewsDAO encapsulates data access and manipulation It represents the Data
 * Access Object (DAO) Pattern. A DAO abstract the mechanisms of accessing and
 * manipulating a persistence store such as database,
 * 
 * using SQLParameter-like processor
 * 
 * @author TUYEN-DM
 * @version $Revision: $ $Date: 2009/05/25 $
 */
public class NewsDAO_AccessImpl implements NewsDao {

	private DbSQLCommon clsDbSQLCommon;

	protected static String SELECT_SQL = "";
	protected static String SEARCH_SQL = "";
	protected static String INSERT_SQL = "";
	protected static String UPDATE_SQL = "";
	protected static String DELETE_SQL = "";
	protected static String DELETE_ID_SQL = "";

	/**
	 * Constructor
	 */
	public NewsDAO_AccessImpl() {
		clsDbSQLCommon = new DbSQLCommon();
	}

	/**
	 * Search news by news name
	 * 
	 * @param newsTitle
	 * @return
	 * @throws DAOException
	 */
	public NewsAdminBean fetchDetail(String newsTitle) throws DAOException {

		PreparedStatement pSmnt = null;

		Connection conn = getMySQLConnnection();
		NewsAdminBean news = null;

		try {
			SELECT_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",	"news.select");
			pSmnt = conn.prepareStatement(SELECT_SQL);
			pSmnt.setString(1, newsTitle);// set parameter 1

			ResultSet set = pSmnt.executeQuery();
			if (set.next()) {
				news = extractNewsDetailFromResultSet(set);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { newsTitle };
			throw new DAOException(se, "error.database.fetch.newsdetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return news;
	}

	/**
	 * Search news by newsID 2009/05/09
	 * 
	 * @param newsID
	 * @return
	 * @throws DAOException
	 */
	public NewsAdminBean fetchDetailID(int newsID) throws DAOException {

		PreparedStatement pSmnt = null;

		Connection conn = getMySQLConnnection();
		NewsAdminBean news = null;

		try {
			SELECT_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"news.selectID");
			pSmnt = conn.prepareStatement(SELECT_SQL);
			pSmnt.setInt(1, newsID);// set parameter 1

			ResultSet set = pSmnt.executeQuery();
			if (set.next()) {
				news = extractNewsDetailFromResultSet(set);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { newsID + "" };
			throw new DAOException(se, "error.database.fetch.newsdetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return news;
	}

	/**
	 * 
	 * @author Tuyen-DM
	 * @param newsTitle
	 * @return
	 * @throws DAOException
	 */
	public NewsAdminBean[] find(String newsTitle) throws DAOException {
		PreparedStatement pSmnt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();
		NewsAdminBean news = null;
		List list = new ArrayList();

		try {
			SEARCH_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"news.select");
			pSmnt = conn.prepareStatement(SEARCH_SQL);
			pSmnt.setString(1, "%" + newsTitle + "%");// search LIKE

			ResultSet set = pSmnt.executeQuery();
			while (set.next()) {
				news = extractNewsDetailFromResultSet(set);
				list.add(news);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { newsTitle };
			throw new DAOException(se, "error.database.fetch.newsdetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		NewsAdminBean[] newss = null;
		if (list.size() > 0) {
			newss = (NewsAdminBean[]) list.toArray(new NewsAdminBean[list
					.size()]);
		}

		return newss;
	}

	/**
	 * insert new news into database
	 * 
	 * @param news
	 * @return
	 * @throws DAOException
	 * @throws DuplicateNewsException
	 */
	public int create(NewsAdminBean news) throws DAOException, DuplicateNewsException {
		CommonUtils cmnUtils = new CommonUtils();
		int intInsertedRows = 0;
		PreparedStatement pStmt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			NewsAdminBean det = fetchDetail(news.getNewsTitle());
			if (det != null) {
				intInsertedRows = -1;
				throw new DuplicateNewsException(
						"error.database.news.duplicate", news.getNewsTitle(),
						null, ErrorLevel.WARNING);
			} else {
				INSERT_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
						"news.insert");
				pStmt = conn.prepareStatement(INSERT_SQL);
				
				try {
					pStmt.setString(1, new String(news.getNewsTitle().getBytes("ISO-8859-1"), "UTF-8"));
					pStmt.setString(2, new String(news.getHeader().getBytes("ISO-8859-1"), "UTF-8"));
					pStmt.setString(3, new String(news.getBody().getBytes("ISO-8859-1"), "UTF-8"));
					
					news.setPostDateTime(cmnUtils.getCurrentDateTime());
					pStmt.setString(4, news.getPostDateTime());
					pStmt.setString(5, new String(news.getSource().getBytes("ISO-8859-1"), "UTF-8"));
				} catch (UnsupportedEncodingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				intInsertedRows = pStmt.executeUpdate();
			}

		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { news.getNewsTitle() };
			throw new DAOException(se, "error.database.news.save",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return intInsertedRows;
	}

	/**
	 * update news information
	 * 
	 * @param news
	 * @return
	 * @throws DAOException
	 * @throws DuplicateNewsException
	 */
	public int update(NewsAdminBean news) throws DAOException,
			DuplicateNewsException {
		CommonUtils cmnUtils = new CommonUtils();
		int intUpdatedRows = 0;
		PreparedStatement pStmt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			NewsAdminBean det = fetchDetailID(news.getNewsID());

			if (det != null && !det.getNewsTitle().equals(news.getNewsTitle())) {
				NewsAdminBean det2 = fetchDetail(news.getNewsTitle());
				if (det2 != null
						&& det2.getNewsTitle().equals(news.getNewsTitle())) {
					throw new DuplicateNewsException(
							"error.database.news.duplicate", news
									.getNewsTitle(), null, ErrorLevel.WARNING);
				}
			}

			UPDATE_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"news.update");
			pStmt = conn.prepareStatement(UPDATE_SQL);
			
			try {
				pStmt.setString(1, new String(news.getNewsTitle().getBytes("ISO-8859-1"), "UTF-8"));
				pStmt.setString(2, new String(news.getHeader().getBytes("ISO-8859-1"), "UTF-8"));
				pStmt.setString(3, new String(news.getBody().getBytes("ISO-8859-1"), "UTF-8"));
				
				news.setPostDateTime(cmnUtils.getCurrentDateTime());
				pStmt.setString(4, news.getPostDateTime());
				pStmt.setString(5, new String(news.getSource().getBytes("ISO-8859-1"), "UTF-8"));
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			pStmt.setInt(6, news.getNewsID());

			intUpdatedRows = pStmt.executeUpdate();

		} catch (SQLException se) {
			String[] valueReplacementArray = { news.getNewsTitle() };
			throw new DAOException(se, "error.database.news.save",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}
		return intUpdatedRows;
	}

	/**
	 * delete news
	 * 
	 * @param id
	 * @throws DAOException
	 */
	public void delete(int id) throws DAOException {
		PreparedStatement pSmnt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			DELETE_ID_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"news.deleteID");
			pSmnt = conn.prepareStatement(DELETE_ID_SQL);
			pSmnt.setInt(1, id);
			pSmnt.executeUpdate();
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { new Integer(id).toString() };
			throw new DAOException(se, "error.database.news.delete",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}
	}

	/**
	 * @param rs
	 *            <p>
	 *            Set results to data object: news
	 *            </p>
	 * 
	 */
	protected NewsAdminBean extractNewsDetailFromResultSet(ResultSet rs)
			throws SQLException {
		NewsAdminBean news = new NewsAdminBean();
		
		news.setNewsID(rs.getInt("NewsID"));		
		news.setNewsTitle(rs.getString("NewsTitle"));
		news.setHeader(rs.getString("Header"));
		news.setBody(rs.getString("Body"));
		news.setPostDateTime(rs.getString("PostDateTime"));
		news.setSource(rs.getString("Source"));

		return news;
	}

	/**
	 * database connection processor
	 */
	protected Connection getConnection() throws DAOException {
		Connection dbConnection = null;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			dbConnection = DriverManager
					.getConnection("jdbc:odbc:helloworld_TRAINING");

		} catch (ClassNotFoundException cfe) {
			throw new DAOException(cfe, "error.database.config", null,
					ErrorLevel.FATAL);
		} catch (SQLException se) {
			throw new DAOException(se, "error.database.config", null,
					ErrorLevel.FATAL);
		}
		return dbConnection;
	}

	/**
	 * @author Tuyen-DM
	 *         <p>
	 *         Mysql connection test
	 *         </p>
	 * @return
	 * @throws DAOException
	 */
	protected Connection getMySQLConnnection() throws DAOException {
		Connection dbConnection = null;
		try {
			// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			// dbConnection =
			// DriverManager.getConnection("jdbc:odbc:helloworld_TRAINING");
			// get database properties test
			Properties prop = new Properties();
			ResourceBundle resource = ResourceBundle.getBundle("db-config");
			Enumeration enumTemp = resource.getKeys();
			while (enumTemp.hasMoreElements()) {
				String key = (String) enumTemp.nextElement();
				prop.put(key, resource.getString(key));
			}

			String userName = prop.getProperty("username");
			String password = prop.getProperty("userpassword");
			String url = prop.getProperty("database_url");
			Class.forName(prop.getProperty("database_Server"));
			dbConnection = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");

		} catch (ClassNotFoundException cfe) {
			throw new DAOException(cfe, "error.database.config", null,
					ErrorLevel.FATAL);
		} catch (SQLException se) {
			throw new DAOException(se, "error.database.config", null,
					ErrorLevel.FATAL);
		}
		return dbConnection;
	}

	/**
	 * debug test
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			NewsDAO_AccessImpl dao = new NewsDAO_AccessImpl();
			NewsAdminBean news = dao.fetchDetail("1");
			System.out.println(news);
		} catch (DAOException de) {
			// TODO: handle exception
			Log log = LogFactory.getLog("NewsDAO");
			de.log(log);
		}
	}
}
